package mvapp;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Vector;

public class DataModel {
	public DataModel() throws SQLException, ClassNotFoundException{
		super();
		this.connectToDB();
	}

	private final int COLUMN = 9;
	private Connection conn;
	private Vector<Vector<String>> re = new Vector<Vector<String>>();
	private final List<String> TITLES = Arrays.asList("Time","First", "Second", "Third", "Fourth", "fifth", "sixth","seventh","Eighth");

    // 连接数据库
	public void connectToDB() throws SQLException, ClassNotFoundException {
		Class.forName("org.mariadb.jdbc.Driver");
		final String URL = "jdbc:mariadb://localhost:3306/mydb?characterEncoding=UTF-8";
		conn = DriverManager.getConnection(URL, "root", "jkl123");
	}


    /**
     * 根据界面选择的查询选项构造查询的ＳＱＬ语句，返回给ＵＩ显示
     */

//构造查询语句
	public String buildSQLQuery(ArrayList<String> conditions){
		StringBuilder sb = new StringBuilder();
		sb.append("select * from class");
		int length = conditions.size();
		if (length != 0)
			sb.append(" where ");
		for (int i = 0; i < length; i++) {
			sb.append(conditions.get(i));
			if (i != length - 1)
				sb.append(" AND ");
		}
		sb.append(";");
		String queryString = sb.toString();
		return queryString;
	}

//获得查询结果
	public Vector<Vector<String>> getQueryResult(String qs) {
		re.clear();
		Statement stmt;
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(qs);
			Vector<String> record;
			while (rs.next()) {
				record = new Vector<String>();
				for (int i = 0; i < COLUMN; i++) {
					record.add(rs.getString(i + 1));
				}
				re.add(record);
			}
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		return re;
	}

//构造插入语句
	public String buildSQLInsertStatement(String Time,String First,String Second,String Third,String Fourth,String Fifth,String Sixth,String Seventh,String Eighth) {

		String cmd = "" ;
		if(Time != null){
			cmd = "insert into class values ('" + Time + "', '"  + First + "', '" + Second + "', '"  + Third  + "', '" +  Fourth  + "', '" + Fifth  + "', '"  + Sixth  + "', '"  + Seventh  + "', '"  + Eighth + "');";

		}
		return cmd;
	}

//构造更新语句
	public String buildSQLUpdateStatement(int col, String val, String Time) {
		// 在文本框显示 SQL 命令
		String cmd = "update class set " + TITLES.get(col) + " = ";
		cmd += (TITLES.get(col) == "Time") ? val : "'" + val + "'";
		cmd += " where Time = '" + Time + "';";
		return cmd;
	}

//构造删除语句
	public String buildSQLDeleteStatement(String Time) {
		String sql = "delete from class where Time = '" + Time + "';";

		return sql;

	}

//添加SQL语句
	public int exeSQLStatement(String sql) {
		int rs = 0;
		Statement stmt;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeUpdate(sql);
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		return rs;
	}

}
